
if exists (select * from sys.objects where name='UP_PCategory_SetPHotKeyWords' and type='P')
drop proc UP_PCategory_SetPHotKeyWords
go

create Proc UP_PCategory_SetPHotKeyWords --UP_PCategory_SetPHotKeyWords 'Add',1,1,6
(
	@Action NVARCHAR(10),
	@Sort int,
	@LanguageID int,
	@CategoryID int
)
as
	
	IF @Action = 'Add'
	BEGIN
	
		DECLARE @Parent int
		--DECLARE @LanguageID int
		--set  @LanguageID = 1 
		;with temp
		as
		(
			select CategoryID,ParentID,CategoryName from PCategory where CategoryID=@CategoryID and LanguageID=@LanguageID
			union all
			select a.CategoryID,a.ParentID,a.CategoryName from PCategory a inner join temp on a.CategoryID=temp.ParentID 
			where a.LanguageID=@LanguageID
		) select @Parent=CategoryID from temp where ParentID=0

		Declare @PTable table
		(
			CategoryID int,
			ParentID int,
			SortOrder int,
			LanguageID int,
			CategoryName varchar(200)
		)

		insert into @PTable 
		select a.CategoryID,a.ParentID,a.SortOrder,a.LanguageID,b.CategoryName from 
		PHotKeyWords as A inner join PCategory as B on a.CategoryID=b.CategoryID and b.LanguageID=@LanguageID
		union all 
		select CategoryID,ParentID,1 SortOrder,@LanguageID LanguageID,CategoryName from (
		select rank() over(partition by parentid order by CategoryID) rowID,* from PCategory where parentid in (
		select CategoryID from PCategory where LanguageID=@LanguageID and parentid=0 and isLeaf=0
		) and LanguageID=@LanguageID 
		)tmp where rowid=1 and parentid not in (select parentid from PHotKeyWords) 
		
		insert into @PTable 
		select CategoryID,@Parent ParentID,1 SortOrder,@LanguageID LanguageID,CategoryName 
		from PCategory where CategoryID=6 and LanguageID=@LanguageID

		if exists(
			select ParentID from @PTable 
			where ParentID=@Parent 
			group by ParentID having count(CategoryID)<5 and sum(len(CategoryName))<20
		)
			INSERT INTO PHotKeyWords (CategoryID,ParentID,SortOrder,LanguageID)
			values (@CategoryID,@Parent,1,@LanguageID)
	END
	ELSE IF @Action = 'DELETE'
	BEGIN
		delete from PHotKeyWords where CategoryID = @CategoryID and LanguageID=@LanguageID
	END
	ELSE
	BEGIN
		update PHotKeyWords set SortOrder=@Sort where CategoryID = @CategoryID and LanguageID=@LanguageID 
	END